// core.module.DB.mysqldb.js   mysql

var mysql = require("mysql");
var logger = require('log4js').getLogger("baseDao");
var util = require("util");
var $conf = require('../../../config/index').db;
var pool = null;
var db = null;

/*
 * 通用数据库操作
 *
 * @tablename 表名
 * @util DBUtil
 * @fiedls 表字段
 */
function Table(tablename, util, fields){
    this.tablename = tablename;
    this.fields = fields;
    this.pool = util.pool;
}
/*
 *  检查表字段
 *
 *  @values 表名
 */
Table.prototype.checkTable=function(values){
    if(values && this.fields){
        var flag=false;
        this.fields.forEach(function(r){
            for(var prop in values){
                if(r===prop|| r.name===prop){
                    flag=true;
                }
            }
            if(!flag){
                return false;
            }
        });
    }
    return true;
};
/*
 *  clear表字段
 *
 *  @tablename 表名
 *  @callback 回调
 */
Table.prototype.clearTable=function(values){
    if(values && this.fields){
        for(var prop in values){
            var flag=false;
            var prop_;
            for(var i=0;i<this.fields.length;i++){
                var r=this.fields[i];
                if(r===prop|| r.name===prop){
                    flag=true;
                    prop_=prop;
                    break;
                }
            }
            if(!flag){
                console.log("-------------------"+prop_);
                delete values[prop];
            }
        }
    }
    return true;
};
/*
 *  获取数据链接
 *
 *  @callback 回调
 */
Table.prototype.getConnection = function(callback) {
    if(!callback){
        callback=function(){};
    }
    this.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('数据库连接异常'+err);
            throw err;
        }
        callback(connection);
    });
};
/*
 *  保存
 *
 *  @values 表名
 *  @callback 回调
 */
Table.prototype.insert = function(obj, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    if (this.clearTable(obj)) {
        this.getConnection(function(connection) {
            var query = connection.query("insert into " + me.tablename + " set ?", obj.contents, function(err, result) {
                if (err) {
                    callback(err,null);
                }else{
                    callback(null,result);
                }
                connection.release(); // 释放连接
            });
            logger.debug(query.sql);
        })
    }
};
/*
 *  根据id查询
 *
 *  @ID
 *  @callback 回调
 */
Table.prototype.get = function(obj, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    if (obj != null && obj != "") {
        this.getConnection(function(connection) {
            var query = connection.query("select * from " + me.tablename + " where "+obj.name+"=?", obj.value, function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); // 释放连接
            });
            logger.debug(query.sql);
        });
    }
};
/*
 *  更新
 *
 *  @values 值
 *  @callback 回调
 */
Table.prototype.update = function(obj, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    if (this.clearTable(obj)) {
        this.getConnection(function(connection) {
            var query = connection.query("update  " + me.tablename + " set ? where "+obj.name+"=" + connection.escape(obj.value), obj.contents, function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); // 释放连接
            });
            logger.debug(query.sql);
        });
    }
};
/*
 *  删除
 *
 *  @ ID
 *  @callback 回调
 */
Table.prototype.remove = function(obj, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    if (obj != null && obj != "") {
        this.getConnection(function(connection) {
            var query = connection.query("delete  from  " + me.tablename + "  where "+obj.name+"=?", obj.value, function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); // 释放连接
            });
            logger.debug(query.sql);
        });
    }
};
/*
 *  exists
 *
 *  @tablename 表名
 *  @callback 回调
 */
Table.prototype.exists = function(tablename, callback) {
    if(!callback){
        callback=function(){};
    }
    if (tablename) {
        this.getConnection(function(connection) {
            var sql = "select table_name from information_schema.tables where table_schema='" + config_.database + "' and table_name='" + tablename + "'";
            var query = connection.query(sql, function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); // 释放连接
            });
            logger.debug(query.sql);
        });
    }
};
/*
 *  clear
 *
 *  @tablename 表名
 *  @callback 回调
 */
Table.prototype.clear = function(tablename, callback) {
    if(!callback){
        callback=function(){};
    }
    if (tablename) {
        this.getConnection(function(connection) {
            var sql = "TRUNCATE TABLE " + tablename;
            var query = connection.query(sql, function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); // 释放连接
            });
            logger.debug(query.sql);
        });
    }
};
/*
 *  数量查询
 *
 *  @callback 回调
 */
Table.prototype.count = function(callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    this.getConnection(function(connection) {
        var query = connection.query("select count(*) as count from " + me.tablename, function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result[0].count);
            }
            connection.release(); // 释放连接
        });
        logger.debug(query.sql);
    });
};
/*
 *  数量查询
 *
 *  @sql sql语句
 *  @p 排序参数
 *  @callback 回调
 */
Table.prototype.countBySql = function(sql, p,callback) {
    if((typeof p == 'function') && p.constructor == Function){
        callback=p;
    }else{
        if (p) {
            for (var i=0;i<p.length;i++) {
                sql=sql.replace("?",this.pool.escape(p[i]));
            }
        }
    }
    if(!callback){
        callback=function(){};
    }
    this.getConnection(function(connection) {
        var query = connection.query("select count(*) as count from ( " + sql + " ) T", function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result[0].count);
            }
            connection.release(); // 释放连接
        });
        logger.debug(query.sql);
    });
};
/*
 *  条件查询
 *
 *  @params 条件参数
 *  @orders 排序参数
 *  @callback 回调
 */
Table.prototype.where = function(params, orders, callback) {
    if(!callback){
        callback=function(){};
    }
    var sql = "select * from " + this.tablename + " where 1=1";
    if (this.clearTable(params)) { // 参数
        for (var pro in params) {
            sql += " and " + pro + "=" + this.pool.escape(params[pro]);
        }
    }
    if(orders){//排序
        for (var pro in orders) {
            sql+=" order by "+pro+" " + orders[pro];
        }
    }
    if((typeof orders == 'function') && orders.constructor == Function){
        callback=orders;
    }
    this.getConnection(function(connection) {
        var query = connection.query(sql, function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result);
            }
            connection.release(); // 释 放 连 接
        });
        logger.debug(query.sql);
    });
};
/*
 *  查询
 *
 *  @callback 回调
 */
Table.prototype.queryAll = function(callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    this.getConnection(function(connection) {
        var query = connection.query("select * from " + me.tablename, function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result);
            }
            connection.release(); // 释放连接
        });
        logger.debug(query.sql);
    });
};
/*
 *  条件查询
 *
 *  @sql sql语句
 *  @page 分页
 *  @params 条件参数
 *  @callback 回调
 */
Table.prototype.queryPageBySql = function(sql, page, params, callback) {
    if(!callback){
        callback=function(){};
    }
    if(params && (typeof params == 'function') && params.constructor == Function){
        callback=params;
    }

    if ((params)) {
        for (var i=0;i<params.length;i++) {
            sql = sql + ' and ' + params[i];
        }
    }
    var me=this;
    this.countBySql(sql, function(err,result) {
        if(err){
            callback(err,result);
        }else{
            // 计数
            page.totalCount = result||0;
            page.totalPage = Math.ceil(page.totalCount / page.pageSize);


            if(page.currentPage == 1){
                sql = "select * from  ( " + sql + " ) T limit " + page.pageSize + "";
            }else{
                sql = "select * from  ( " + sql + " ) T limit " + ((page.currentPage-1) * page.pageSize) + "," + page.end + "";
                page.end = page.currentPage * page.pageSize;
            }
            // 分页
            me.getConnection(function(connection) {
                var query = connection.query(sql, function(err, result) {
                    if (err) {
                        callback(err, result);
                    }else{
                        page.data = result;
                        callback(null, page);
                    }
                    connection.release(); // 释放连接
                });
                logger.debug(query.sql);
            });
        }
    });
};
/*
 *  分页条件查询
 *
 *  @page 分页
 *  @params 条件参数
 *  @callback 回调
 */
Table.prototype.queryPage = function(page, params, callback) {
    if(!callback){
        callback=function(){};
    }
    var sql = "select * from " + this.tablename + " where 1=1";

    this.queryPageBySql(sql, page, params, callback);
};
/*
 *  执行语句
 *
 *  @sql sql语句
 *  @params 条件参数
 *  @callback 回调
 */
Table.prototype.executeSql = function(sql, params, callback) {
    if(!callback){
        callback=function(){};
    }
    if ((params)) {
        for (var i=0;i<params.length;i++) {
            sql=sql.replace("?",this.pool.escape(params[i])) ;
        }
    }
    this.getConnection(function(connection) {
        var query = connection.query(sql, function(err, result) {
            if (err) {
                callback(err, result);
            }else{
                callback(null, result);
            }
            connection.release(); // 释放连接
        });
        logger.debug(query.sql);
    });
};
/*
 *  创建连接池
 *
 *  @return pool
 */
var createPool = function() {
    if(pool==null){
        pool=mysql.createPool($conf.mysql); // 连接池
    }
    return pool;
};
/*
 *  查询工具类
 */
function DBUtil() {
    this.pool = createPool();
    this.tables = [];
}
DBUtil.prototype.define = function(table) {
    // var me=this;
    // if(util.isArray(table)){
    //     table.forEach(function(r){
    //         me.tables.push([r.key,new Table(r.name,me,r.fields)]);
    //     });
    // }else{
    //     me.tables.push([table.key,new Table(table.name,me,table.fields)]);
    // }
    var me = this;
    return new Table(table, me, table.fields);
};
DBUtil.prototype.get=function(tablename){
    if(tablename){
        var _len=this.tables.length;
        for(i=0;i<_len;i++){
            if(this.tables[i][0]==tablename){
                return  this.tables[i][1];
            }
        }
    }
    return null;
};
exports.Instance = function() {
    if (db == null) {
        db = new DBUtil();
    }
    return db;
};

// 测试
// var db = this.Instance();
// var User = db.define('t_user');
// User.get('1',function(error, result){
//     if(error) console.log(error);
//     console.log("result:"+result);
// })
//
// User.queryAll(function(error, result){
//     if(error) console.log(error);
//     console.log('result:'+result.toString());
// });




//  第一版
// var mysql = require('mysql');
// var $conf = require('../../conf/db');
// var pool  = mysql.createPool($conf.mysql);  // 连接池
// var log = require('log4js').getLogger("baseDao.js");
//
// module.exports = {
//     exec:function(sql, params, callback){
//         if(callback){
//             if (!sql) {
//                 callback(null, "sql not find!");
//                 return;
//             }
//             pool.getConnection(function(err, connection) {
//                 connection.query(sql, params, function(err, result) {
//                     if (err) {
//                         log.debug('sql:'+sql+' | params:'+params+' | error:'+err);
//                         callback(err, null);
//                         return;
//                     };
//                     log.debug(result);
//                     connection.release(); // 释放连接
//                     callback(err, result);
//                 });
//             });
//         }else{
//             log.debug('callback is undefained!');
//         }
//     }
// };
// // 测试
// var $sql = require('./activityPageSqlMapping');
// var basedao = require('./baseDao');
// basedao.exec($sql.queryById,[1],function(error, result){
//     if(error){
//         console.log(error);
//     }else{
//         console.log(result);
//     }
// })